<?php

/**
 * Subclass for performing query and update operations on the 'articulo' table.
 *
 * 
 *
 * @package lib.model
 */ 
class ArticuloPeer extends BaseArticuloPeer
{

	static function getAllReorden($pagina = 1){
		
		$c = new Criteria();
    $c->add(ArticuloPeer::EXISTENCIA, ArticuloPeer::EXISTENCIA.'<='.ArticuloPeer::REORDEN, Criteria::CUSTOM);
    $pager = new sfPropelPager('Articulo', 20);
    $pager->setCriteria( $c );
    $pager->setPage($pagina);
    $pager->init();
    return $pager;
	}

	static function getAll($pagina = 1, $sort, $find, $find2, $find3 , $find4){
		$pager = new sfPropelPager('Articulo', 20);
		$c = new Criteria();
		if($find != null)
		  $c->add(ArticuloPeer::CODIGO,"%$find%", Criteria::LIKE);
		if($find2 != null)
      $c->add(ArticuloPeer::NOMBRE,"%$find2%", Criteria::LIKE);
    if($find3 != null)
      $c->add(ProveedorPeer::NOMBRE,"%$find3%", Criteria::LIKE);
    if($find4 != null)
      $c->add(ArticuloPeer::EXISTENCIA, ArticuloPeer::EXISTENCIA.'<='.ArticuloPeer::REORDEN, Criteria::CUSTOM);
		if($sort != null)
		  $c->addAscendingOrderByColumn($sort);
    $pager->setCriteria($c);
    $pager->setPeerMethod('doSelectJoinAll');
    $pager->setPage($pagina);
    $pager->init();    
		return $pager;
	}
	
  static function getArtByCategoria($cat , $orden = 1 , $pagina = 1){
    $c = new Criteria();
    if( $cat != 'todos')
    {
      $c->add(CategoriaPeer::NOMBRE, $cat );
      $c->addJoin(ArticuloPeer::CATEGORIA_ID, CategoriaPeer::ID);
    }
    switch($orden){
    	case 1:
    		$c->addAscendingOrderByColumn(ArticuloPeer::PRECIO);
    		break;
    	case 2:
    		$c->addDescendingOrderByColumn(ArticuloPeer::PRECIO);
    		break;
    	case 3:
    		$c->addDescendingOrderByColumn(ArticuloPeer::FECHA_COMPRA);
    }
    $pager = new sfPropelPager('Articulo', 20);
    $pager->setCriteria( $c );
    $pager->setPage($pagina);
    $pager->init();
    return $pager;
  }
  
  static function getArtBySearch($search, $orden = 1, $pagina = 1){
  	$c = new Criteria();
    $c->add(ArticuloPeer::DESCRIPCION,'%'.$search.'%',Criteria::LIKE);
    switch($orden){
      case 1:
        $c->addAscendingOrderByColumn(ArticuloPeer::PRECIO);
        break;
      case 2:
        $c->addDescendingOrderByColumn(ArticuloPeer::PRECIO);
        break;
      case 3:
        $c->addDescendingOrderByColumn(ArticuloPeer::FECHA_COMPRA);
    }
    $pager = new sfPropelPager('Articulo', 20);
    $pager->setCriteria( $c );
    $pager->setPage($pagina);
    $pager->init();
    return $pager;
  }
	
  static function getArtByUltimos(){
  	$c = new Criteria();
    //$c->add( ArticuloPeer::FECHA_COMPRA, date('Y-m-d') );
    $c->addAscendingOrderByColumn(ArticuloPeer::FECHA_COMPRA);
    $c->setLimit(4);
    return ArticuloPeer::doSelect($c);
  }
  
  static function getArtByVendidos(){
  	$conexion = Propel::getConnection(ArticuloPeer::DATABASE_NAME);
  	$consulta = $conexion->prepare("SELECT ".ArticuloPeer::ID." , ".ArticuloPeer::DESCRIPCION.", ".ArticuloPeer::FOTO.", ".ArticuloPeer::PRECIO.", sum(".ArticuloVentaPeer::CANTIDAD.
  	                                ") as CANTIDAD FROM ".ArticuloPeer::TABLE_NAME." , ".ArticuloVentaPeer::TABLE_NAME." WHERE ".
  	                                ArticuloPeer::ID." = ".ArticuloVentaPeer::ID." GROUP BY ".ArticuloVentaPeer::ID." ORDER BY CANTIDAD DESC");
  	/*$consulta->bindValue(1,ArticuloPeer::ID);
  	$consulta->bindValue(2,ArticuloPeer::DESCRIPCION);
  	$consulta->bindValue(3,ArticuloPeer::FOTO);
  	$consulta->bindValue(4,ArticuloPeer::PRECIO);
  	$consulta->bindValue(5,ArticuloVentaPeer::CANTIDAD);
  	//$consulta->bindValue(6,ArticuloPeer::TABLE_NAME);
  	$consulta->bindValue(6,ArticuloVentaPeer::TABLE_NAME);*/
  	//$consulta->bindValue(8,ArticuloPeer::ID);
  	//$consulta->bindValue(9,ArticuloVentaPeer::ID);
  	//$consulta->bindValue(10,ArticuloVentaPeer::ID);
  	$consulta->execute();
  	return $consulta->fetchAll();    
  }
  
  static function getRelacionados($id){
  	$c = new Criteria();
  	$c->addJoin(ArticuloPeer::ID,SugerenciaPeer::RELACIONADO);
    $c->add(SugerenciaPeer::ARTICULO_ID,$id);
    $c->setLimit(4);
    return ArticuloPeer::doSelect($c);
  }
  
	static function getTotal(){
		return ArticuloPeer::doCount(new Criteria);
	}
	
	static function getIdByCodigo($codigo){
		$c = new Criteria();
		$c->add(ArticuloPeer::CODIGO,$codigo);
		$a = ArticuloPeer::doSelectOne($c);
		return $a->getId();
		
	}
	
	static function getColores($codigo){
		$c = new Criteria();
		$c->add(ArticuloPeer::CODIGO,$codigo);
		//$c->addSelectColumn(ArticuloPeer::COLOR);
		$rs =  ArticuloPeer::doSelectStmt($c);
		$result = array();
		$res = $rs->fetchAll(PDO::FETCH_ASSOC);
		foreach($res as $r)
    {
      $result[$r['ID']] = $r['COLOR'];
    } 
    return $result;
	}
	
  static function getTallas($codigo){
    $c = new Criteria();
    $c->add(ArticuloPeer::CODIGO,$codigo);
    //$c->addSelectColumn(ArticuloPeer::COLOR);
    $rs =  ArticuloPeer::doSelectStmt($c);
    $result = array();
    $res = $rs->fetchAll(PDO::FETCH_ASSOC);
    foreach($res as $r)
    {
      $result[$r['ID']] = $r['TALLA'];
    } 
    return $result;
  }
  
  static function getTopMasVendidos($limit = 10){
  	
  	$con = Propel::getConnection();
    $stmt = $con->prepare(" SELECT * , sum( cantidad ) as cant
FROM `articulo`
JOIN articulo_venta ON articulo.id = articulo_venta.articulo_id
JOIN venta ON venta.id = articulo_venta.venta_id
GROUP BY codigo
LIMIT 0 , 10");
    //$stmt->bindValue(1, $name);
    $stmt->execute();
    $rest = $stmt->fetchAll();
    /*while($row = $stmt->fetch()) { 
      print "Name: " . $row['name'] . "\n";
    }*/
    return $rest;
  }
  
  static function array_sum_key( $arr, $index = null ){
    if(!is_array( $arr ) || sizeof( $arr ) < 1){
        return 0;
    }
    $ret = 0;
    foreach( $arr as $id => $data ){
        if( isset( $index )  ){
            $ret += (isset( $data[$index] )) ? $data[$index] : 0;
        }else{
            $ret += $data;
        }
    }
    return $ret;
  }
  
  static function getReporteMes(){
  	$con = Propel::getConnection();
  	$anio = date('Y');
  	$mes = date('m');
    $stmt = $con->prepare("SELECT *  FROM `venta` WHERE created_at >= '$anio-$mes-01'");
    //$stmt->bindValue(1, $name);
    $stmt->execute();
    $res = $stmt->fetchAll();
    /*while($row = $stmt->fetch()) { 
      print "Name: " . $row['name'] . "\n";
    }*/
    $res["total"] = ArticuloPeer::array_sum_key($res, 'total');    
    return $res;
  }
  
  static function getNumVentas($fecha1, $fecha2){
  	$c = new Criteria();
  	$c->add(VentaPeer::CREATED_AT, VentaPeer::CREATED_AT."<='{$fecha2}'", Criteria::CUSTOM);
  	$c->addAnd(VentaPeer::CREATED_AT, VentaPeer::CREATED_AT.">='$fecha1'", Criteria::CUSTOM);
  	return VentaPeer::doCount( $c );
  }
  
  static function getNumArticuloVendidos($fecha1, $fecha2){
  	$con = Propel::getConnection();
    $stmt = $con->prepare("SELECT sum( cantidad )
FROM `venta` , `articulo_venta`
WHERE (
venta.CREATED_AT <= '$fecha2'
AND venta.CREATED_AT >= '$fecha1'
)
AND articulo_venta.VENTA_ID = venta.ID ");    
    $stmt->execute();
    $rest = $stmt->fetch(PDO::FETCH_NUM);
  	return $rest[0];
  }
	
  static function getTotalVenta($fecha1, $fecha2){
  	$con = Propel::getConnection();
    $stmt = $con->prepare("SELECT sum(total)
FROM `venta`
WHERE (
venta.CREATED_AT <= '$fecha2'
AND venta.CREATED_AT >= '$fecha1'
)");    
    $stmt->execute();
    $rest = $stmt->fetch(PDO::FETCH_NUM);
    return $rest[0];
  }
  
  static function getTopClientes($fecha1, $fecha2){
  	$con = Propel::getConnection();
    $stmt = $con->prepare("SELECT *,sum(total) as suma
FROM `venta`
WHERE (
venta.CREATED_AT <= '$fecha2'
AND venta.CREATED_AT >= '$fecha1'
) LIMIT 0,10");    
    $stmt->execute();
    $rest = $stmt->fetchAll();
    return $rest;
  }
  
  static function getVentas($fecha1, $fecha2){
  	$c = new Criteria();
    $c->add(VentaPeer::CREATED_AT, VentaPeer::CREATED_AT."<='{$fecha2}'", Criteria::CUSTOM);
    $c->addAnd(VentaPeer::CREATED_AT, VentaPeer::CREATED_AT.">='$fecha1'", Criteria::CUSTOM);
    return ArticuloVentaPeer::doSelectJoinAll($c );  	
  }
static function getArticulosVendidos($fecha1, $fecha2){
    $con = Propel::getConnection();
    $stmt = $con->prepare("SELECT articulo_venta.ARTICULO_ID, sum( articulo_venta.CANTIDAD ) AS cant, venta.CREATED_AT, articulo.CODIGO, articulo.NOMBRE, articulo.EXISTENCIA, articulo.PRECIO
FROM `articulo_venta`
LEFT JOIN venta ON ( articulo_venta.VENTA_ID = venta.ID )
LEFT JOIN articulo ON ( articulo_venta.ARTICULO_ID = articulo.ID )
WHERE (
venta.CREATED_AT <= '$fecha2'
AND venta.CREATED_AT >= '$fecha1'
)
GROUP BY articulo_venta.articulo_id
ORDER BY `articulo_venta`.`ARTICULO_ID` ASC
LIMIT 0 , 30 ");    
    $stmt->execute();
    $rest = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $rest;   
  }
}
